USE <database_name>;
GO

SELECT
	sm.object_id,
	OBJECT_NAME(sm.object_id) AS 'object_name',
	o.type,
	o.type_desc,
	sm.definition
FROM sys.sql_modules AS sm
INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition LIKE '%SEARCH_WORD_HERE%' -- COLLATE SQL_Latin1_General_CP1_CI_AS
ORDER BY o.type ASC;